
import pandas
import pymysql
import xlwt, xlrw, xlrd

#
# # 读取数据
# import xlrd
# import datetime
# from datetime import date
#
#
# def read_excel():
#     #打开文件
#     wb = xlrd.open_workbook(r'../../student.xlsx')
#     #获取所有sheet的名字
#     print(wb.sheet_names())
#     #获取第二个sheet的表明
#     sheet2 = wb.sheet_names()[0]
#     print('sheet2', sheet2)
#     #sheet1索引从0开始，得到sheet1表的句柄
#     sheet1 = wb.sheet_by_index(0)
#     rowNum = sheet1.nrows
#     colNum = sheet1.ncols
#     #s = sheet1.cell(1,0).value.encode('utf-8')
#     s = sheet1.cell(1,0).value
#     #获取某一个位置的数据
#     print(s)
#     #print(s.decode('utf-8'))
#     #获取整行和整列的数据
#     #第二行数据
#     row2 = sheet1.row_values(1)
#     #第二列数据
#     cols2 = sheet1.col_values(2)
#     #python读取excel中单元格内容为日期的方式
#     #返回类型有5种
#     for i in range(rowNum):
#         if sheet1.cell(i,2).ctype == 3:
#             d = xlrd.xldate_as_tuple(sheet1.cell_value(i,2),wb.datemode)
#         print(date(*d[:3]),end='')
#     print('\n')
#
#
# if __name__ == '__main__':
#     read_excel()


import pandas as pd

info_list = ['sno', 'name', 'gender', 'birthday', 'mobile', 'email', 'address']
# 定义空列表用来存放学生信息
stu_list = []

data = pd.read_excel('../../student.xlsx')
print('行索引', data.index)
print('列索引', data.columns)
dict = {}
for i in range(0, len(data)):
    for n, item in enumerate(info_list):
        # 构造字典数据
        dict[item] = data.iloc[i, n]
    stu_list.append(dict)
print('列表数据', stu_list)
print(data)

# 导出

conn = pymysql.connect(
    host='localhost',
    user='root',
    passwd='',
    db='student',
    port=3306,
    charset='utf8')
# from StudentBE.settings import DATABASES

# 读取数据
data = pd.read_sql('select * from student', conn)
data.to_excel('../../new_student.xlsx')
print(data)
